package com.relationalcloud;

import java.io.File;
import java.io.IOException;
import java.util.Vector;

import org.apache.commons.io.FileUtils;

import com.relationalcloud.tsqlparser.Parser;
import com.relationalcloud.tsqlparser.exception.NotImplementedException;
import com.relationalcloud.tsqlparser.expression.BinaryExpression;
import com.relationalcloud.tsqlparser.expression.Expression;
import com.relationalcloud.tsqlparser.expression.operators.relational.EqualsTo;
import com.relationalcloud.tsqlparser.expression.operators.relational.GreaterThan;
import com.relationalcloud.tsqlparser.expression.operators.relational.GreaterThanEquals;
import com.relationalcloud.tsqlparser.expression.operators.relational.MinorThan;
import com.relationalcloud.tsqlparser.expression.operators.relational.MinorThanEquals;
import com.relationalcloud.tsqlparser.loader.ForeignKey;
import com.relationalcloud.tsqlparser.loader.IntegrityConstraint;
import com.relationalcloud.tsqlparser.loader.IntegrityConstraintsExistsException;
import com.relationalcloud.tsqlparser.loader.PrimaryKey;
import com.relationalcloud.tsqlparser.loader.Schema;
import com.relationalcloud.tsqlparser.parser.ParseException;
import com.relationalcloud.tsqlparser.schema.Column;
import com.relationalcloud.tsqlparser.loader.SchemaTable;
import com.relationalcloud.tsqlparser.statement.select.SelectExpressionItem;

public class SQL2Category {


	public Schema schema = null;
//	public static String PREFIX = "/Library/WebServer/Documents/";
	public static String PREFIX = "/var/www/cat/";
		
		
	
	public SQL2Category() throws IntegrityConstraintsExistsException{
		schema = new Schema(null,"tpcc",null,null,null,null);
		SchemaTable t = new SchemaTable();
		t.setTableName("A");
		t.addColumn("a1","int");
		t.addColumn("a2","int");
		t.addColumn("a3","String");
		schema.addTable(t);

		Vector<String> fields1 = new Vector<String>();
		fields1.add("a1");
		t.addConstraint(new PrimaryKey("pk1", fields1));

		SchemaTable t2 = new SchemaTable();
		t2.setTableName("B");
		t2.addColumn("b1","int");
		t2.addColumn("b2","String");
		t2.addColumn("b3","int");
		schema.addTable(t2);

		Vector<String> fields2 = new Vector<String>();
		fields2.add("b1");
		t2.addConstraint(new PrimaryKey("pk2", fields2));

		t2.addConstraint( new ForeignKey("fk1",fields2, "A",fields1));

	}

	public static void main(String[] args) throws ParseException, NotImplementedException, IntegrityConstraintsExistsException, IOException {

		String sql = "SELECT A.a2 as YY, A.a3, B.b3 FROM A, B WHERE A.a1=B.b1 AND A.a2=\"3\" AND B.b2>\"test\";";

		SQL2Category s2 = new SQL2Category();
		Parser p = new Parser("tpcc",s2.schema, sql);

		String schemaAdditionalCons = generateSchemaGraph(s2);
		String querySelectAdditionalCons = generateQuerySelectionGraph(p,s2.schema);
		String queryProjectAdditionalCons = generateQueryProjectGraph(s2, p);

		generateHTMLPage(sql, s2, schemaAdditionalCons, querySelectAdditionalCons, queryProjectAdditionalCons);


	}

	public static void generateHTMLPage(String sql, SQL2Category s2,
			String schemaAdditionalCons, String querySelectAdditionalCons,
			String queryProjectAdditionalCons) throws IOException {
		String html = "<html><body><h1> SQL 2 Category translator </h1> " +
		"<hr><h2> SCHEMA </h2><p>" +
		s2.schema.toString()+
		"</p>" +
		"<img src=\"schema.gif\" />" +
		"<p><font face=\"courier\">"+schemaAdditionalCons+"</font></p>"+
		"<hr><h2> QUERY </h2><font face=\"courier\">" +
		sql+
		"</font></p>" +

		"<table><tr><td><img src=\"schema.gif\" /></td><td><img src=\"right.gif\" /></td><td><img src=\"querySelect.gif\" /></td><td><img src=\"left.gif\" /></td><td><img src=\"queryProject.gif\" /></td></tr>" +
		"<tr><td>"+schemaAdditionalCons+"</td><td>IDENTITY</td><td>"+querySelectAdditionalCons+"</td><td>"+ queryProjectAdditionalCons +" </td></tr>"+
		"</table></html></body>";

		File file = new File(PREFIX +"index.html");
		FileUtils.writeStringToFile(file, html);
	}

	public static String generateQueryProjectGraph(SQL2Category s2, Parser p)
	throws NotImplementedException {
		GraphViz gv = new GraphViz();
		gv.addln(gv.start_graph());

		gv.addln("subgraph cluster_0 {");
		String queryProjectAdditionalCons = printQueryProjection(p,s2.schema,gv);
		gv.addln("}");

		gv.addln(gv.end_graph());
		File out = new File(PREFIX +"queryProject.gif");
		System.out.println(gv.getDotSource());
		gv.writeGraphToFile(gv.getGraph(gv.getDotSource()), out);
		return queryProjectAdditionalCons;
	}

	public static String generateSchemaGraph(SQL2Category s2) {
		GraphViz gv = new GraphViz();
		gv.addln(gv.start_graph());
		gv.addln("subgraph cluster_0 {");

		String schemaAdditionalCons= printSchema(s2.schema,gv);

		gv.addln("}");

		gv.addln(gv.end_graph());
		File out = new File(PREFIX + "schema.gif");
		
		System.out.println("WRITING TO: "+ PREFIX + "schema.gif");
		
		System.out.println(gv.getDotSource());
		gv.writeGraphToFile(gv.getGraph(gv.getDotSource()), out);
		return schemaAdditionalCons;
	}

	public static String generateQuerySelectionGraph(Parser p, Schema schema) throws NotImplementedException {


		GraphViz gv = new GraphViz();
		gv.addln(gv.start_graph());

		gv.addln("subgraph cluster_0 {");
		String schemaCons = printSchema(p.getSchema(),gv);
		String querySelectAdditionalCons = schemaCons +"<br>";

		gv.addln("Q [fontcolor=red,color=red];");
		for(String s:p.getTableStringList()){
			gv.addln("\tQ->" + s +"[fontcolor=red,color=red,label=\"q_"+s+"\"];");

		}

		for(BinaryExpression be:p.getAllBinaryPredicates()){
			Expression l = be.getLeftExpression();
			Expression r = be.getRightExpression();

			String op = "?";

			if(be instanceof EqualsTo)
				op = "=";
			if(be instanceof GreaterThan)
				op = ">";
			if(be instanceof GreaterThanEquals)
				op = ">=";
			if(be instanceof MinorThanEquals)
				op = "<=";
			if(be instanceof MinorThan)
				op = "<";



			if(l instanceof Column && !(r instanceof Column)){
				//gv.addln("\t" + r.toString().replaceAll("'", "") + "->" + ((Column)l).getColumnName() +";");
				gv.addln("\t"+r.toString().replaceAll("'", "") + "->" + schema.getTable(((Column)l).getTable().getName()).getColType(((Column)l).getColumnName()) +"[fontfontcolor=red,color=red,fontcolor=red,color=red,label=\""+r.toString().replaceAll("'", "")+"\"];");
				gv.addln("\tQ->" + r.toString().replaceAll("'", "")+"[fontcolor=red,color=red,label=\"q_"+r.toString().replaceAll("'", "")+"\"];");
				gv.addln(r.toString().replaceAll("'", "") + "[fontcolor=red,color=red,label=\""+op+r.toString().replaceAll("'", "")+"\"];");
				gv.addln(schema.getTable(((Column)l).getTable().getName()).getColType(((Column)l).getColumnName()) +" [fontcolor=blue,color=blue];");
				querySelectAdditionalCons+= r.toString().replaceAll("'", "") + "&bull;"+ "q_"+r.toString().replaceAll("'", "") +" = "+ ((Column)l).getColumnName() +"&bull;"+"q_"+ schema.getTable(((Column)l).getTable().getName()).getTableName() +"<br>";
			}
			if(r instanceof Column && !(l instanceof Column)){
				//gv.addln("\t" + l.toString().replaceAll("'", "") + "->" + ((Column)r).getColumnName() +";" );
				gv.addln("\t"+l.toString().replaceAll("'", "") + "->" + schema.getTable(((Column)l).getTable().getName()).getColType(((Column)l).getColumnName()) +"[fontcolor=red,color=red,label=\""+l.toString().replaceAll("'", "")+"\"];");
				gv.addln("\tQ->" + l.toString().replaceAll("'", "")+"[fontcolor=red,color=red,label=\"q_"+r.toString().replaceAll("'", "")+"\"];");
				gv.addln(l.toString().replaceAll("'", "") + "[label=\""+op+l.toString().replaceAll("'", "")+"\"];");
				gv.addln(schema.getTable(((Column)r).getTable().getName()).getColType(((Column)r).getColumnName()) +" [fontcolor=blue,color=blue]");
				querySelectAdditionalCons+= l.toString().replaceAll("'", "") + "&bull;"+ "q_"+l.toString().replaceAll("'", "") +" = "+ ((Column)r).getColumnName() +"&bull;"+"q_"+ schema.getTable(((Column)r).getTable().getName()).getTableName()+"<br>";


			}




		}




		for(BinaryExpression be:p.getJoinPredicates()){
			Expression l = be.getLeftExpression();
			Expression r = be.getRightExpression();

			if(r instanceof Column && l instanceof Column){


				//gv.addln("\t"+((Column)r).getColumnName() + "->" + 
				//                   ((Column)l).getColumnName() +" [fontcolor=red,color=red,dir=both];" );

				querySelectAdditionalCons+=  ((Column)r).getColumnName() +"&bull;"+"q_"+ schema.getTable(((Column)r).getTable().getName()).getTableName()+ " = "+
				((Column)l).getColumnName() +"&bull;"+"q_"+ schema.getTable(((Column)l).getTable().getName()).getTableName()+ ";\n";

			}
		}

		gv.addln("}");
		gv.addln(gv.end_graph());
		File out = new File(PREFIX +"querySelect.gif");
		System.out.println(gv.getDotSource());
		gv.writeGraphToFile(gv.getGraph(gv.getDotSource()), out);

		return querySelectAdditionalCons;
	}



	private static String printQueryProjection(Parser p, Schema schema, GraphViz gv) throws NotImplementedException {

		String queryProjectAdditionalCons = "";
		for(Object s:p.getSelectTargetList().toArray()){
			if(s instanceof SelectExpressionItem)

				for(SchemaTable t:schema.getTables())
					if(t.hasColumn(((SelectExpressionItem)s).getColumnName())){
						String type = t.getColType(((SelectExpressionItem)s).getColumnName());

						String colName = ((SelectExpressionItem)s).getAlias();
						if(colName == null)
							colName = ((SelectExpressionItem)s).getColumnName();

						gv.addln("\tQ-> " + type + "[fontcolor=red,color=red,label=\""+colName+"\"];" +";");
						gv.addln(type + "[fontcolor=blue,color=blue]");

						queryProjectAdditionalCons += colName+" ==> "+((SelectExpressionItem)s).getColumnName() + "&bull;" + "q_"+t.getTableName() +"<br>";

					}  
			gv.addln("Q [fontcolor=red,color=red];");    

		}

		return queryProjectAdditionalCons;
	}


	private static String printSchema(Schema schema,GraphViz gv) {

		String additionalCons = "COMMUTE:<br>";


		for(SchemaTable t:schema.getTables()){


			PrimaryKey pk = null;
			for(IntegrityConstraint ic:t.getConstraints()){
				if(ic instanceof ForeignKey){
					gv.addln("\t"+t.getTableName() + "->" +((ForeignKey) ic).getTargetTable()+"[label=\""+ic.getId()+"\"];");        

					additionalCons+=((ForeignKey)ic).getSourceColumns().firstElement() + " = " +((ForeignKey)ic).getTargetColumns().firstElement() +"&bull;"  +ic.getId()+"; ";
					//add loop for multi attribute
					//gv.addln("\t"+((ForeignKey)ic).getSourceColumns().firstElement() + "->" +((ForeignKey)ic).getTargetColumns().firstElement()+";");
				}
				if(ic instanceof PrimaryKey){
					pk = (PrimaryKey)ic;
				}
			}

			for(String c:t.getColumns()){
				if(pk!=null && pk.getFields().firstElement().equals(c))
					gv.addln("\t"+t.getTableName()+"->"+t.getColType(c)+" [label=\""+c+"\", dir=both, arrowtail=halfopen];");
				else  
					gv.addln("\t"+t.getTableName()+"->"+t.getColType(c) +" [label=\""+c+"\"];");
				//gv.addln("\t"+c +"[shape=point];");
				//gv.addln("\t"+c +"->" +t.getColType(c)+";");
				gv.addln(t.getColType(c) +" [fontcolor=blue,color=blue]");

			}

		}

		return additionalCons;


	}
}
